import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple
from decimal import Decimal

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历_民族分组_毕业去向落实率(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历，民族分组，毕业去向落实率 - 指标计算函数
    
    ## 指标说明
    本指标用于统计特定项目中不同学历层次（本科、专科、硕士、博士）以及总体毕业生的
    汉族和少数民族的毕业去向落实率及详细去向分布。数据来源为学生客观表，按照学历和民族合并字段分组统计，
    包含两级毕业去向分类：
    1. 一级分类（grad_dest_merge）：单位就业、灵活就业、升学、待就业等大类
    2. 二级分类（grad_dest）：签就业协议形式就业、境内升学、境外留学等详细类型
    同时计算毕业去向不为'待就业'和'暂不就业'的学生占比作为落实率。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值：本科毕业生、专科毕业生、硕士研究生、博士研究生
        
    ## 示例
    ### 输入示例1：不指定学历（返回所有学历明细）
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出示例1：所有学历明细（含一级、二级分类）
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "group": "博士研究生",
                "汉族": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 150,
                            "一级占比": 0.8333,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 100, "占比": 0.5556},
                                {"二级分类": "签劳动合同形式就业", "人数": 50, "占比": 0.2778}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 20,
                            "一级占比": 0.1111,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 15, "占比": 0.0833},
                                {"二级分类": "境外留学", "人数": 5, "占比": 0.0278}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 10,
                            "一级占比": 0.0556,
                            "明细": [
                                {"二级分类": "待就业", "人数": 10, "占比": 0.0556}
                            ]
                        }
                    ],
                    "总人数": 180,
                    "落实率": 0.9444
                },
                "少数民族": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 40,
                            "一级占比": 0.8000,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 30, "占比": 0.6000},
                                {"二级分类": "签劳动合同形式就业", "人数": 10, "占比": 0.2000}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 8,
                            "一级占比": 0.1600,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 6, "占比": 0.1200},
                                {"二级分类": "境外留学", "人数": 2, "占比": 0.0400}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 2,
                            "一级占比": 0.0400,
                            "明细": [
                                {"二级分类": "待就业", "人数": 2, "占比": 0.0400}
                            ]
                        }
                    ],
                    "总人数": 50,
                    "落实率": 0.9600
                },
                "总体": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 190,
                            "一级占比": 0.8261,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 130, "占比": 0.5652},
                                {"二级分类": "签劳动合同形式就业", "人数": 60, "占比": 0.2609}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 28,
                            "一级占比": 0.1217,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 21, "占比": 0.0913},
                                {"二级分类": "境外留学", "人数": 7, "占比": 0.0304}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 12,
                            "一级占比": 0.0522,
                            "明细": [
                                {"二级分类": "待就业", "人数": 12, "占比": 0.0522}
                            ]
                        }
                    ],
                    "总人数": 230,
                    "落实率": 0.9478
                }
            },
            {
                "group": "总体",
                "汉族": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 2000,
                            "一级占比": 0.7752,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 1300, "占比": 0.5039},
                                {"二级分类": "签劳动合同形式就业", "人数": 600, "占比": 0.2326},
                                {"二级分类": "应征义务兵", "人数": 100, "占比": 0.0388}
                            ]
                        },
                        {
                            "一级分类": "灵活就业",
                            "一级人数": 300,
                            "一级占比": 0.1163,
                            "明细": [
                                {"二级分类": "其他录用形式就业", "人数": 200, "占比": 0.0775},
                                {"二级分类": "自由职业", "人数": 100, "占比": 0.0388}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 100,
                            "一级占比": 0.0388,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 80, "占比": 0.0310},
                                {"二级分类": "境外留学", "人数": 20, "占比": 0.0078}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 180,
                            "一级占比": 0.0698,
                            "明细": [
                                {"二级分类": "待就业", "人数": 180, "占比": 0.0698}
                            ]
                        }
                    ],
                    "总人数": 2580,
                    "落实率": 0.9302
                },
                "少数民族": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 480,
                            "一级占比": 0.7385,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 320, "占比": 0.4923},
                                {"二级分类": "签劳动合同形式就业", "人数": 150, "占比": 0.2308},
                                {"二级分类": "应征义务兵", "人数": 10, "占比": 0.0154}
                            ]
                        },
                        {
                            "一级分类": "灵活就业",
                            "一级人数": 70,
                            "一级占比": 0.1077,
                            "明细": [
                                {"二级分类": "其他录用形式就业", "人数": 50, "占比": 0.0769},
                                {"二级分类": "自由职业", "人数": 20, "占比": 0.0308}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 50,
                            "一级占比": 0.0769,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 40, "占比": 0.0615},
                                {"二级分类": "境外留学", "人数": 10, "占比": 0.0154}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 50,
                            "一级占比": 0.0769,
                            "明细": [
                                {"二级分类": "待就业", "人数": 50, "占比": 0.0769}
                            ]
                        }
                    ],
                    "总人数": 650,
                    "落实率": 0.9231
                },
                "总体": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 2480,
                            "一级占比": 0.7678,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 1620, "占比": 0.5016},
                                {"二级分类": "签劳动合同形式就业", "人数": 750, "占比": 0.2322},
                                {"二级分类": "应征义务兵", "人数": 110, "占比": 0.0341}
                            ]
                        },
                        {
                            "一级分类": "灵活就业",
                            "一级人数": 370,
                            "一级占比": 0.1146,
                            "明细": [
                                {"二级分类": "其他录用形式就业", "人数": 250, "占比": 0.0774},
                                {"二级分类": "自由职业", "人数": 120, "占比": 0.0372}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 150,
                            "一级占比": 0.0464,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 120, "占比": 0.0372},
                                {"二级分类": "境外留学", "人数": 30, "占比": 0.0093}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 230,
                            "一级占比": 0.0712,
                            "明细": [
                                {"二级分类": "待就业", "人数": 230, "占比": 0.0712}
                            ]
                        }
                    ],
                    "总人数": 3230,
                    "落实率": 0.9288
                }
            }
        ]
    }
    ```
    
    ### 输入示例2：指定学历
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "本科毕业生"
    }
    ```
    
    ### 输出示例2：指定学历数据
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "group": "本科毕业生",
                "汉族": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 700,
                            "一级占比": 0.6364,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 450, "占比": 0.4091},
                                {"二级分类": "签劳动合同形式就业", "人数": 200, "占比": 0.1818},
                                {"二级分类": "应征义务兵", "人数": 50, "占比": 0.0455}
                            ]
                        },
                        {
                            "一级分类": "灵活就业",
                            "一级人数": 180,
                            "一级占比": 0.1636,
                            "明细": [
                                {"二级分类": "其他录用形式就业", "人数": 120, "占比": 0.1091},
                                {"二级分类": "自由职业", "人数": 60, "占比": 0.0545}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 120,
                            "一级占比": 0.1091,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 100, "占比": 0.0909},
                                {"二级分类": "境外留学", "人数": 20, "占比": 0.0182}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 100,
                            "一级占比": 0.0909,
                            "明细": [
                                {"二级分类": "待就业", "人数": 100, "占比": 0.0909}
                            ]
                        }
                    ],
                    "总人数": 1100,
                    "落实率": 0.9091
                },
                "少数民族": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 150,
                            "一级占比": 0.5357,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 100, "占比": 0.3571},
                                {"二级分类": "签劳动合同形式就业", "人数": 50, "占比": 0.1786}
                            ]
                        },
                        {
                            "一级分类": "灵活就业",
                            "一级人数": 60,
                            "一级占比": 0.2143,
                            "明细": [
                                {"二级分类": "其他录用形式就业", "人数": 40, "占比": 0.1429},
                                {"二级分类": "自由职业", "人数": 20, "占比": 0.0714}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 20,
                            "一级占比": 0.0714,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 18, "占比": 0.0643},
                                {"二级分类": "境外留学", "人数": 2, "占比": 0.0071}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 50,
                            "一级占比": 0.1786,
                            "明细": [
                                {"二级分类": "待就业", "人数": 50, "占比": 0.1786}
                            ]
                        }
                    ],
                    "总人数": 280,
                    "落实率": 0.8214
                },
                "总体": {
                    "data": [
                        {
                            "一级分类": "单位就业",
                            "一级人数": 850,
                            "一级占比": 0.6159,
                            "明细": [
                                {"二级分类": "签就业协议形式就业", "人数": 550, "占比": 0.3986},
                                {"二级分类": "签劳动合同形式就业", "人数": 250, "占比": 0.1812},
                                {"二级分类": "应征义务兵", "人数": 50, "占比": 0.0362}
                            ]
                        },
                        {
                            "一级分类": "灵活就业",
                            "一级人数": 240,
                            "一级占比": 0.1739,
                            "明细": [
                                {"二级分类": "其他录用形式就业", "人数": 160, "占比": 0.1159},
                                {"二级分类": "自由职业", "人数": 80, "占比": 0.0580}
                            ]
                        },
                        {
                            "一级分类": "升学",
                            "一级人数": 140,
                            "一级占比": 0.1014,
                            "明细": [
                                {"二级分类": "境内升学", "人数": 118, "占比": 0.0855},
                                {"二级分类": "境外留学", "人数": 22, "占比": 0.0159}
                            ]
                        },
                        {
                            "一级分类": "待就业",
                            "一级人数": 150,
                            "一级占比": 0.1087,
                            "明细": [
                                {"二级分类": "待就业", "人数": 150, "占比": 0.1087}
                            ]
                        }
                    ],
                    "总人数": 1380,
                    "落实率": 0.8913
                }
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历，民族分组，毕业去向落实率, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 构建学生表名
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        
        # 4. 处理学历参数（空字符串转为None）
        if education == '':
            education = None
        
        # 5. 构建学历筛选条件
        education_condition = ""
        if education:
            if education not in ["本科毕业生", "专科毕业生", "硕士研究生", "博士研究生"]:
                raise ValueError(f"学历参数错误，必须是: 本科毕业生、专科毕业生、硕士研究生、博士研究生")
            education_condition = f"AND education = '{education}'"
        
        logger.info(f"学历筛选: {education if education else '不限制，返回所有学历明细'}")
        
        # 6. 定义查询函数：查询指定学历的民族分组落实率及各毕业去向分布（含一级、二级分类）
        def query_nation_settlement_rate(edu_condition: str, group_name: str) -> Dict[str, Any]:
            """查询指定学历的民族分组毕业去向落实率及详细去向分布（含一级、二级分类）"""
            # 查询各毕业去向的详细数据（一级和二级分类）
            nation_dest_sql = f"""
            SELECT
                nation_merge,
                grad_dest_merge,
                grad_dest,
                COUNT(*) as count
            FROM
                {student_table}
            WHERE
                shard_tb_key = %s
                {edu_condition}
            GROUP BY nation_merge, grad_dest_merge, grad_dest
            """
            nation_dest_results = db.fetchall(nation_dest_sql, (shard_tb_key,))
            
            if not nation_dest_results:
                logger.warning(f"分组 '{group_name}': 未找到学生民族统计数据")
                return {
                    "group": group_name,
                    "汉族": {"data": [], "总人数": 0, "落实率": 0.0},
                    "少数民族": {"data": [], "总人数": 0, "落实率": 0.0},
                    "总体": {"data": [], "总人数": 0, "落实率": 0.0}
                }
            
            # 初始化数据结构 {一级分类: {人数, 二级明细}}
            han_level1_data = {}
            minority_level1_data = {}
            han_total = 0
            minority_total = 0
            
            # 处理查询结果，按一级分类分组
            for result in nation_dest_results:
                nation = result['nation_merge']
                level1 = result['grad_dest_merge']  # 一级分类
                level2 = result['grad_dest']  # 二级分类
                count = result['count']
                
                if nation == '汉族':
                    han_total += count
                    if level1 not in han_level1_data:
                        han_level1_data[level1] = {
                            '一级人数': 0,
                            '明细': []
                        }
                    han_level1_data[level1]['一级人数'] += count
                    han_level1_data[level1]['明细'].append({
                        '二级分类': level2,
                        '人数': count
                    })
                else:
                    minority_total += count
                    if level1 not in minority_level1_data:
                        minority_level1_data[level1] = {
                            '一级人数': 0,
                            '明细': []
                        }
                    minority_level1_data[level1]['一级人数'] += count
                    minority_level1_data[level1]['明细'].append({
                        '二级分类': level2,
                        '人数': count
                    })
            
            # 计算汉族的各项占比和落实率
            han_result = []
            han_settled = 0
            for level1, level1_info in han_level1_data.items():
                level1_count = level1_info['一级人数']
                level1_ratio = level1_count / han_total if han_total > 0 else 0
                
                # 计算二级分类的占比
                level2_details = []
                for level2_item in level1_info['明细']:
                    level2_count = level2_item['人数']
                    level2_ratio = level2_count / han_total if han_total > 0 else 0
                    level2_details.append({
                        '二级分类': level2_item['二级分类'],
                        '人数': level2_count,
                        '占比': round(float(level2_ratio), 4)
                    })
                
                han_result.append({
                    '一级分类': level1,
                    '一级人数': level1_count,
                    '一级占比': round(float(level1_ratio), 4),
                    '明细': level2_details
                })
                
                if level1 not in ['待就业', '暂不就业']:
                    han_settled += level1_count
            
            han_settlement_rate = han_settled / han_total if han_total > 0 else 0
            
            # 计算少数民族的各项占比和落实率
            minority_result = []
            minority_settled = 0
            for level1, level1_info in minority_level1_data.items():
                level1_count = level1_info['一级人数']
                level1_ratio = level1_count / minority_total if minority_total > 0 else 0
                
                # 计算二级分类的占比
                level2_details = []
                for level2_item in level1_info['明细']:
                    level2_count = level2_item['人数']
                    level2_ratio = level2_count / minority_total if minority_total > 0 else 0
                    level2_details.append({
                        '二级分类': level2_item['二级分类'],
                        '人数': level2_count,
                        '占比': round(float(level2_ratio), 4)
                    })
                
                minority_result.append({
                    '一级分类': level1,
                    '一级人数': level1_count,
                    '一级占比': round(float(level1_ratio), 4),
                    '明细': level2_details
                })
                
                if level1 not in ['待就业', '暂不就业']:
                    minority_settled += level1_count
            
            minority_settlement_rate = minority_settled / minority_total if minority_total > 0 else 0
            
            # 计算总体数据（合并汉族和少数民族）
            total_count = han_total + minority_total
            total_settled = han_settled + minority_settled
            total_rate = total_settled / total_count if total_count > 0 else 0
            
            # 合并总体的一级分类数据
            total_level1_data = {}
            for result in nation_dest_results:
                level1 = result['grad_dest_merge']
                level2 = result['grad_dest']
                count = result['count']
                
                if level1 not in total_level1_data:
                    total_level1_data[level1] = {
                        '一级人数': 0,
                        '明细': []
                    }
                total_level1_data[level1]['一级人数'] += count
                total_level1_data[level1]['明细'].append({
                    '二级分类': level2,
                    '人数': count
                })
            
            # 计算总体的占比
            total_result = []
            for level1, level1_info in total_level1_data.items():
                level1_count = level1_info['一级人数']
                level1_ratio = level1_count / total_count if total_count > 0 else 0
                
                # 合并相同二级分类的数据
                level2_merged = {}
                for item in level1_info['明细']:
                    level2_name = item['二级分类']
                    if level2_name not in level2_merged:
                        level2_merged[level2_name] = 0
                    level2_merged[level2_name] += item['人数']
                
                level2_details = []
                for level2_name, level2_count in level2_merged.items():
                    level2_ratio = level2_count / total_count if total_count > 0 else 0
                    level2_details.append({
                        '二级分类': level2_name,
                        '人数': level2_count,
                        '占比': round(float(level2_ratio), 4)
                    })
                
                total_result.append({
                    '一级分类': level1,
                    '一级人数': level1_count,
                    '一级占比': round(float(level1_ratio), 4),
                    '明细': level2_details
                })
            
            return {
                "group": group_name,
                "汉族": {
                    "data": han_result,
                    "总人数": han_total,
                    "落实率": round(float(han_settlement_rate), 4)
                },
                "少数民族": {
                    "data": minority_result,
                    "总人数": minority_total,
                    "落实率": round(float(minority_settlement_rate), 4)
                },
                "总体": {
                    "data": total_result,
                    "总人数": total_count,
                    "落实率": round(float(total_rate), 4)
                }
            }
        
        # 7. 计算各学历和总体数据
        result = []
        
        # 定义学历排序顺序
        education_order = {
            "博士研究生": 1,
            "硕士研究生": 2,
            "本科毕业生": 3,
            "专科毕业生": 4
        }
        
        # 7.1 如果指定了学历，只返回该学历的数据
        if education:
            logger.info(f"开始计算学历 '{education}' 的民族分组落实率")
            result.append(query_nation_settlement_rate(f"AND education = '{education}'", education))
        else:
            # 7.2 如果没有指定学历，返回所有学历的明细数据
            logger.info("开始计算所有学历的民族分组落实率明细")
            education_list = ["本科毕业生", "专科毕业生", "硕士研究生", "博士研究生"]
            
            education_data = []
            for edu in education_list:
                logger.info(f"计算学历 '{edu}' 的民族分组落实率")
                edu_data = query_nation_settlement_rate(f"AND education = '{edu}'", edu)
                # 只添加有数据的学历（检查总体的总人数）
                if edu_data["总体"]["总人数"] > 0:
                    education_data.append(edu_data)
            
            # 按照学历排序（博士 > 硕士 > 本科 > 专科）
            education_data.sort(key=lambda x: education_order.get(x["group"], 999))
            result.extend(education_data)
            
            # 7.3 计算总体数据
            logger.info("开始计算总体的民族分组落实率")
            total_data = query_nation_settlement_rate("", "总体")
            if total_data["总体"]["总人数"] > 0:
                result.append(total_data)

        logger.info(f"指标 '各学历，民族分组，毕业去向落实率' 计算成功，返回 {len(result)} 条记录")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历，民族分组，毕业去向落实率' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历，民族分组，毕业去向落实率",
            "code": 500,
            "error": str(e)
        }

